const express = require('express');
const { promisePool } = require('../config/database');
const { getServiceItemsSchema, getServiceCategoriesSchema, serviceItemIdSchema, serviceCategoryIdSchema, addFavoriteSchema, removeFavoriteSchema, getUserFavoritesSchema, searchServiceItemsSchema, getUserFavoritesCountSchema } = require('../schema/serviceItems');

const router = express.Router();

// 获取服务事项列表
router.get('/items', async (req, res) => {
  try {
    console.log('原始查询参数:', req.query);
    console.log('参数类型:', typeof req.query.limit, typeof req.query.page);
    
    // 验证查询参数
    const { error, value } = getServiceItemsSchema.validate(req.query);
    if (error) {
      console.error('参数验证失败:', error.details);
      return res.status(400).json({
        success: false,
        message: error.details[0].message
      });
    }

    console.log('验证后的参数:', value);
    console.log('验证后参数类型:', typeof value.limit, typeof value.page);
    
    // 额外验证：确保limit和page是有效的数字
    if (typeof value.limit !== 'number' || typeof value.page !== 'number') {
      console.error('分页参数类型错误:', { limit: value.limit, page: value.page });
      return res.status(400).json({
        success: false,
        message: '分页参数必须是有效数字'
      });
    }

    const {
      category_id,
      keywords,
      is_online,
      is_appointment,
      status,
      page,
      limit
    } = value;
    
    console.log('解构后的参数:', { category_id, keywords, is_online, is_appointment, status, page, limit });

    // 构建WHERE条件字符串 - 使用参数化查询
    let whereConditions = [
      'si.deleted_at IS NULL',
      'sc.deleted_at IS NULL',
      'si.status = ?'
    ];
    let queryParams = [status];

    // 分类筛选
    if (category_id) {
      whereConditions.push('si.category_id = ?');
      queryParams.push(category_id);
    }

    // 在线办理筛选
    if (is_online !== undefined) {
      whereConditions.push('si.is_online = ?');
      queryParams.push(is_online ? 1 : 0);
    }

    // 可预约筛选
    if (is_appointment !== undefined) {
      whereConditions.push('si.is_appointment = ?');
      queryParams.push(is_appointment ? 1 : 0);
    }

    // 关键词搜索
    if (keywords && keywords.trim()) {
      const searchTerm = keywords.trim();
      whereConditions.push('(si.title LIKE ? OR si.description LIKE ? OR si.department LIKE ?)');
      queryParams.push(`%${searchTerm}%`, `%${searchTerm}%`, `%${searchTerm}%`);
    }

    const whereClause = `WHERE ${whereConditions.join(' AND ')}`;

    // 计算偏移量
    const offset = (page - 1) * limit;

    // 确保limit和offset是整数类型
    const limitInt = parseInt(limit, 10);
    const offsetInt = parseInt(offset, 10);

    // 查询服务事项列表 - 使用参数化查询
    const itemsQuery = `
      SELECT 
        si.id,
        si.category_id,
        sc.name as category_name,
        si.title,
        si.description,
        si.department,
        si.is_online,
        si.is_appointment,
        si.status,
        si.sort_order,
        si.created_at,
        si.updated_at
      FROM service_items si
      INNER JOIN service_categories sc ON si.category_id = sc.id
      ${whereClause}
      ORDER BY sc.sort_order, si.sort_order, si.created_at DESC
      LIMIT ${limitInt} OFFSET ${offsetInt}
    `;
    
    // 验证转换后的值是否有效
    if (isNaN(limitInt) || isNaN(offsetInt) || limitInt <= 0 || offsetInt < 0) {
      console.error('无效的分页参数:', { limit, offset, limitInt, offsetInt });
      return res.status(400).json({
        success: false,
        message: '分页参数无效'
      });
    }
    
    console.log('SQL查询:', itemsQuery);
    console.log('查询参数:', queryParams);

    const [items] = await promisePool.execute(itemsQuery, queryParams);

    // 查询总数 - 使用参数化查询
    const countQuery = `
      SELECT COUNT(*) as total
      FROM service_items si
      INNER JOIN service_categories sc ON si.category_id = sc.id
      ${whereClause}
    `;

    const [countResult] = await promisePool.execute(countQuery, queryParams);
    const total = countResult[0].total;

    // 格式化返回数据
    const formattedItems = items.map(item => ({
      id: item.id,
      cateId: item.category_id,
      cateName: item.category_name,
      title: item.title,
      desc: item.description,
      dept: item.department,
      online: Boolean(item.is_online),
      appoint: Boolean(item.is_appointment),
      status: item.status,
      sortOrder: item.sort_order,
      createdAt: item.created_at,
      updatedAt: item.updated_at
    }));

    res.json({
      success: true,
      data: {
        items: formattedItems,
        pagination: {
          page,
          limit,
          total,
          totalPages: Math.ceil(total / limit)
        }
      },
      message: '获取服务事项列表成功'
    });

  } catch (error) {
    console.error('获取服务事项列表失败:', error);
    console.error('错误详情:', error.message);
    console.error('错误堆栈:', error.stack);
    res.status(500).json({
      success: false,
      message: '服务器内部错误',
      error: process.env.NODE_ENV === 'development' ? error.message : undefined
    });
  }
});

// 获取服务分类列表
router.get('/categories', async (req, res) => {
  try {
    // 验证查询参数
    const { error, value } = getServiceCategoriesSchema.validate(req.query);
    if (error) {
      return res.status(400).json({
        success: false,
        message: error.details[0].message
      });
    }

    const { status, include_stats } = value;

    let query;
    let queryParams = [];

    if (include_stats) {
      // 包含统计信息的查询
      query = `
        SELECT 
          sc.id,
          sc.name,
          sc.description,
          sc.sort_order,
          sc.status,
          sc.created_at,
          sc.updated_at,
          COUNT(si.id) as total_items,
          COUNT(CASE WHEN si.is_online = 1 THEN 1 END) as online_items,
          COUNT(CASE WHEN si.is_appointment = 1 THEN 1 END) as appointment_items,
          COUNT(CASE WHEN si.status = 'active' THEN 1 END) as active_items
        FROM service_categories sc
        LEFT JOIN service_items si ON sc.id = si.category_id AND si.deleted_at IS NULL
        WHERE sc.deleted_at IS NULL
      `;
      
      if (status) {
        query += ' AND sc.status = ?';
        queryParams.push(status);
      }
      
      query += ' GROUP BY sc.id, sc.name, sc.description, sc.sort_order, sc.status, sc.created_at, sc.updated_at ORDER BY sc.sort_order';
    } else {
      // 简单查询
      query = `
        SELECT 
          id,
          name,
          description,
          sort_order,
          status,
          created_at,
          updated_at
        FROM service_categories
        WHERE deleted_at IS NULL
      `;
      
      if (status) {
        query += ' AND status = ?';
        queryParams.push(status);
      }
      
      query += ' ORDER BY sort_order';
    }

    console.log('分类查询SQL:', query);
    console.log('分类查询参数:', queryParams);
    
    const [categories] = await promisePool.execute(query, queryParams);
    console.log('分类查询结果:', categories);

    // 格式化返回数据
    const formattedCategories = categories.map(category => ({
      id: category.id,
      name: category.name,
      description: category.description,
      sortOrder: category.sort_order,
      status: category.status,
      createdAt: category.created_at,
      updatedAt: category.updated_at,
      ...(include_stats && {
        totalItems: category.total_items,
        onlineItems: category.online_items,
        appointmentItems: category.appointment_items,
        activeItems: category.active_items
      })
    }));

    res.json({
      success: true,
      data: {
        categories: formattedCategories
      },
      message: '获取服务分类列表成功'
    });

  } catch (error) {
    console.error('获取服务分类列表失败:', error);
    console.error('错误详情:', error.message);
    console.error('错误堆栈:', error.stack);
    res.status(500).json({
      success: false,
      message: '服务器内部错误',
      error: process.env.NODE_ENV === 'development' ? error.message : undefined
    });
  }
});

// 获取单个服务事项详情
router.get('/items/:id', async (req, res) => {
  try {
    // 验证路径参数
    const { error, value } = serviceItemIdSchema.validate({ id: req.params.id });
    if (error) {
      return res.status(400).json({
        success: false,
        message: error.details[0].message
      });
    }

    const { id } = value;

    // 查询服务事项详情
    const query = `
      SELECT 
        si.id,
        si.category_id,
        sc.name as category_name,
        si.title,
        si.description,
        si.department,
        si.is_online,
        si.is_appointment,
        si.status,
        si.sort_order,
        si.created_at,
        si.updated_at
      FROM service_items si
      INNER JOIN service_categories sc ON si.category_id = sc.id
      WHERE si.id = ? AND si.deleted_at IS NULL AND sc.deleted_at IS NULL
    `;

    const [items] = await promisePool.execute(query, [id]);

    if (items.length === 0) {
      return res.status(404).json({
        success: false,
        message: '服务事项不存在'
      });
    }

    const item = items[0];

    // 格式化返回数据
    const formattedItem = {
      id: item.id,
      cateId: item.category_id,
      cateName: item.category_name,
      title: item.title,
      desc: item.description,
      dept: item.department,
      online: Boolean(item.is_online),
      appoint: Boolean(item.is_appointment),
      status: item.status,
      sortOrder: item.sort_order,
      createdAt: item.created_at,
      updatedAt: item.updated_at
    };

    res.json({
      success: true,
      data: {
        item: formattedItem
      },
      message: '获取服务事项详情成功'
    });

  } catch (error) {
    console.error('获取服务事项详情失败:', error);
    res.status(500).json({
      success: false,
      message: '服务器内部错误'
    });
  }
});

// 获取单个服务分类详情
router.get('/categories/:id', async (req, res) => {
  try {
    // 验证路径参数
    const { error, value } = serviceCategoryIdSchema.validate({ id: req.params.id });
    if (error) {
      return res.status(400).json({
        success: false,
        message: error.details[0].message
      });
    }

    const { id } = value;

    // 查询服务分类详情
    const query = `
      SELECT 
        id,
        name,
        description,
        sort_order,
        status,
        created_at,
        updated_at
      FROM service_categories
      WHERE id = ? AND deleted_at IS NULL
    `;

    const [categories] = await promisePool.execute(query, [id]);

    if (categories.length === 0) {
      return res.status(404).json({
        success: false,
        message: '服务分类不存在'
      });
    }

    const category = categories[0];

    // 格式化返回数据
    const formattedCategory = {
      id: category.id,
      name: category.name,
      description: category.description,
      sortOrder: category.sort_order,
      status: category.status,
      createdAt: category.created_at,
      updatedAt: category.updated_at
    };

    res.json({
      success: true,
      data: {
        category: formattedCategory
      },
      message: '获取服务分类详情成功'
    });

  } catch (error) {
    console.error('获取服务分类详情失败:', error);
    res.status(500).json({
      success: false,
      message: '服务器内部错误'
    });
  }
});

// 添加收藏
router.post('/favorites', async (req, res) => {
  try {
    // 验证请求体
    const { error, value } = addFavoriteSchema.validate(req.body);
    if (error) {
      return res.status(400).json({
        success: false,
        message: error.details[0].message
      });
    }

    const { service_item_id, user_id } = value;

    // 检查服务事项是否存在
    const checkItemQuery = 'SELECT id FROM service_items WHERE id = ? AND deleted_at IS NULL AND status = "active"';
    const [items] = await promisePool.execute(checkItemQuery, [service_item_id]);
    
    if (items.length === 0) {
      return res.status(404).json({
        success: false,
        message: '服务事项不存在或已停用'
      });
    }

    // 检查是否已经收藏
    const checkFavoriteQuery = `
      SELECT id FROM favorites 
      WHERE user_id = ? AND service_item_id = ? AND deleted_at IS NULL
    `;
    const [existingFavorites] = await promisePool.execute(checkFavoriteQuery, [user_id, service_item_id]);
    
    if (existingFavorites.length > 0) {
      // 如果已存在，更新状态为active
      const updateQuery = `
        UPDATE favorites 
        SET status = 'active', updated_at = CURRENT_TIMESTAMP 
        WHERE user_id = ? AND service_item_id = ? AND deleted_at IS NULL
      `;
      await promisePool.execute(updateQuery, [user_id, service_item_id]);
      
      return res.json({
        success: true,
        message: '收藏成功'
      });
    }

    // 添加新收藏
    const insertQuery = `
      INSERT INTO favorites (user_id, service_item_id, status, created_at, updated_at)
      VALUES (?, ?, 'active', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)
    `;
    await promisePool.execute(insertQuery, [user_id, service_item_id]);

    res.json({
      success: true,
      message: '收藏成功'
    });

  } catch (error) {
    console.error('添加收藏失败:', error);
    res.status(500).json({
      success: false,
      message: '服务器内部错误'
    });
  }
});

// 取消收藏
router.delete('/favorites', async (req, res) => {
  try {
    // 验证请求体
    const { error, value } = removeFavoriteSchema.validate(req.body);
    if (error) {
      return res.status(400).json({
        success: false,
        message: error.details[0].message
      });
    }

    const { service_item_id, user_id } = value;

    // 检查收藏是否存在
    const checkFavoriteQuery = `
      SELECT id FROM favorites 
      WHERE user_id = ? AND service_item_id = ? AND deleted_at IS NULL
    `;
    const [existingFavorites] = await promisePool.execute(checkFavoriteQuery, [user_id, service_item_id]);
    
    if (existingFavorites.length === 0) {
      return res.status(404).json({
        success: false,
        message: '收藏记录不存在'
      });
    }

    // 软删除收藏记录
    const deleteQuery = `
      UPDATE favorites 
      SET status = 'cancelled', deleted_at = CURRENT_TIMESTAMP, updated_at = CURRENT_TIMESTAMP 
      WHERE user_id = ? AND service_item_id = ? AND deleted_at IS NULL
    `;
    await promisePool.execute(deleteQuery, [user_id, service_item_id]);

    res.json({
      success: true,
      message: '取消收藏成功'
    });

  } catch (error) {
    console.error('取消收藏失败:', error);
    res.status(500).json({
      success: false,
      message: '服务器内部错误'
    });
  }
});

// 获取用户收藏列表
router.get('/favorites', async (req, res) => {
  try {
    console.log('获取用户收藏列表请求:', req.query);
    console.log('收藏查询参数类型:', typeof req.query.user_id, typeof req.query.limit, typeof req.query.page);
    
    // 验证查询参数
    const { error, value } = getUserFavoritesSchema.validate(req.query);
    if (error) {
      console.error('参数验证失败:', error.details);
      return res.status(400).json({
        success: false,
        message: error.details[0].message
      });
    }

    const { user_id, category_id, status, page, limit } = value;
    console.log('验证后的参数:', { user_id, category_id, status, page, limit });
    console.log('验证后参数类型:', typeof user_id, typeof limit, typeof page);

    // 构建WHERE条件
    let whereConditions = [
      'f.user_id = ?',
      'f.deleted_at IS NULL',
      'si.deleted_at IS NULL',
      'sc.deleted_at IS NULL',
      'f.status = ?'
    ];
    let queryParams = [user_id, status];

    // 分类筛选
    if (category_id) {
      whereConditions.push('si.category_id = ?');
      queryParams.push(category_id);
    }

    const whereClause = `WHERE ${whereConditions.join(' AND ')}`;
    console.log('WHERE条件:', whereClause);
    console.log('查询参数:', queryParams);

    // 计算偏移量
    const offset = (page - 1) * limit;

    // 确保limit和offset是整数类型
    const limitInt = parseInt(limit, 10);
    const offsetInt = parseInt(offset, 10);

    // 查询收藏列表
    const favoritesQuery = `
      SELECT 
        f.id as favorite_id,
        f.service_item_id,
        si.title as service_title,
        si.description as service_description,
        si.category_id,
        sc.name as category_name,
        si.department as service_department,
        si.is_online,
        si.is_appointment,
        f.status as favorite_status,
        f.created_at as favorite_time,
        f.updated_at
      FROM favorites f
      INNER JOIN service_items si ON f.service_item_id = si.id
      INNER JOIN service_categories sc ON si.category_id = sc.id
      ${whereClause}
      ORDER BY f.created_at DESC
      LIMIT ${limitInt} OFFSET ${offsetInt}
    `;
    
    console.log('收藏查询SQL:', favoritesQuery);
    console.log('查询参数:', queryParams);
    
    const [favorites] = await promisePool.execute(favoritesQuery, queryParams);
    console.log('查询结果数量:', favorites.length);

    // 查询总数
    const countQuery = `
      SELECT COUNT(*) as total
      FROM favorites f
      INNER JOIN service_items si ON f.service_item_id = si.id
      INNER JOIN service_categories sc ON si.category_id = sc.id
      ${whereClause}
    `;
    
    console.log('计数查询SQL:', countQuery);
    console.log('计数查询参数:', queryParams);
    
    const [countResult] = await promisePool.execute(countQuery, queryParams);
    const total = countResult[0].total;
    console.log('总记录数:', total);

    // 格式化返回数据
    const formattedFavorites = favorites.map(favorite => ({
      favoriteId: favorite.favorite_id,
      serviceItemId: favorite.service_item_id,
      serviceTitle: favorite.service_title,
      serviceDescription: favorite.service_description,
      categoryId: favorite.category_id,
      categoryName: favorite.category_name,
      serviceDepartment: favorite.service_department,
      isOnline: Boolean(favorite.is_online),
      isAppointment: Boolean(favorite.is_appointment),
      favoriteStatus: favorite.favorite_status,
      favoriteTime: favorite.favorite_time,
      updatedAt: favorite.updated_at
    }));

    res.json({
      success: true,
      data: {
        favorites: formattedFavorites,
        pagination: {
          page,
          limit,
          total,
          totalPages: Math.ceil(total / limit)
        }
      },
      message: '获取收藏列表成功'
    });

  } catch (error) {
    console.error('获取收藏列表失败:', error);
    console.error('错误详情:', error.message);
    console.error('错误堆栈:', error.stack);
    
    // 检查是否是表不存在的错误
    if (error.message.includes("doesn't exist") || error.message.includes("Table") || error.code === 'ER_NO_SUCH_TABLE') {
      return res.status(500).json({
        success: false,
        message: '数据库表不存在，请联系管理员初始化数据库'
      });
    }
    
    res.status(500).json({
      success: false,
      message: '服务器内部错误',
      error: process.env.NODE_ENV === 'development' ? error.message : undefined
    });
  }
});

// 搜索服务事项
router.get('/search', async (req, res) => {
  try {
    console.log('搜索服务事项请求参数:', req.query);
    
    // 验证查询参数
    const { error, value } = searchServiceItemsSchema.validate(req.query);
    if (error) {
      console.error('搜索参数验证失败:', error.details);
      return res.status(400).json({
        success: false,
        message: error.details[0].message
      });
    }

    const {
      keyword,
      category_id,
      is_online,
      is_appointment,
      status,
      page,
      limit
    } = value;
    
    console.log('验证后的搜索参数:', { keyword, category_id, is_online, is_appointment, status, page, limit });

    // 构建WHERE条件字符串 - 使用参数化查询
    let whereConditions = [
      'si.deleted_at IS NULL',
      'sc.deleted_at IS NULL',
      'si.status = ?'
    ];
    let queryParams = [status];

    // 分类筛选
    if (category_id) {
      whereConditions.push('si.category_id = ?');
      queryParams.push(category_id);
    }

    // 在线办理筛选
    if (is_online !== undefined) {
      whereConditions.push('si.is_online = ?');
      queryParams.push(is_online ? 1 : 0);
    }

    // 可预约筛选
    if (is_appointment !== undefined) {
      whereConditions.push('si.is_appointment = ?');
      queryParams.push(is_appointment ? 1 : 0);
    }

    // 关键词搜索 - 使用LIKE查询
    if (keyword && keyword.trim()) {
      const searchTerm = keyword.trim();
      // 使用LIKE查询搜索标题、描述和部门
      whereConditions.push('(si.title LIKE ? OR si.description LIKE ? OR si.department LIKE ?)');
      queryParams.push(`%${searchTerm}%`, `%${searchTerm}%`, `%${searchTerm}%`);
    }

    const whereClause = `WHERE ${whereConditions.join(' AND ')}`;

    // 计算偏移量
    const offset = (page - 1) * limit;

    // 确保limit和offset是整数类型
    const limitInt = parseInt(limit, 10);
    const offsetInt = parseInt(offset, 10);

    // 查询服务事项列表 - 使用参数化查询
    const searchQuery = `
      SELECT 
        si.id,
        si.category_id,
        sc.name as category_name,
        si.title,
        si.description,
        si.department,
        si.is_online,
        si.is_appointment,
        si.status,
        si.sort_order,
        si.created_at,
        si.updated_at,
        1 as relevance_score
      FROM service_items si
      INNER JOIN service_categories sc ON si.category_id = sc.id
      ${whereClause}
      ORDER BY sc.sort_order, si.sort_order, si.created_at DESC
      LIMIT ${limitInt} OFFSET ${offsetInt}
    `;
    
    // 验证转换后的值是否有效
    if (isNaN(limitInt) || isNaN(offsetInt) || limitInt <= 0 || offsetInt < 0) {
      console.error('无效的分页参数:', { limit, offset, limitInt, offsetInt });
      return res.status(400).json({
        success: false,
        message: '分页参数无效'
      });
    }
    
    console.log('搜索SQL查询:', searchQuery);
    console.log('搜索查询参数:', queryParams);

    const [items] = await promisePool.execute(searchQuery, queryParams);

    // 查询总数 - 使用参数化查询
    const countQuery = `
      SELECT COUNT(*) as total
      FROM service_items si
      INNER JOIN service_categories sc ON si.category_id = sc.id
      ${whereClause}
    `;

    const [countResult] = await promisePool.execute(countQuery, queryParams);
    const total = countResult[0].total;

    // 格式化返回数据
    const formattedItems = items.map(item => ({
      id: item.id,
      cateId: item.category_id,
      cateName: item.category_name,
      title: item.title,
      desc: item.description,
      dept: item.department,
      online: Boolean(item.is_online),
      appoint: Boolean(item.is_appointment),
      status: item.status,
      sortOrder: item.sort_order,
      createdAt: item.created_at,
      updatedAt: item.updated_at,
      relevanceScore: item.relevance_score || 0
    }));

    res.json({
      success: true,
      data: {
        items: formattedItems,
        pagination: {
          page,
          limit,
          total,
          totalPages: Math.ceil(total / limit)
        },
        searchInfo: {
          keyword: keyword,
          searchTime: new Date().toISOString(),
          resultCount: formattedItems.length
        }
      },
      message: '搜索服务事项成功'
    });

  } catch (error) {
    console.error('搜索服务事项失败:', error);
    console.error('错误详情:', error.message);
    console.error('错误堆栈:', error.stack);
    res.status(500).json({
      success: false,
      message: '服务器内部错误',
      error: process.env.NODE_ENV === 'development' ? error.message : undefined
    });
  }
});

// 获取用户收藏总数
router.get('/favorites/count', async (req, res) => {
  try {
    console.log('获取用户收藏总数请求:', req.query);
    
    // 验证查询参数
    const { error, value } = getUserFavoritesCountSchema.validate(req.query);
    if (error) {
      console.error('参数验证失败:', error.details);
      return res.status(400).json({
        success: false,
        message: error.details[0].message
      });
    }

    const { user_id, status } = value;
    console.log('验证后的参数:', { user_id, status });

    // 查询用户收藏总数
    const countQuery = `
      SELECT COUNT(*) as total_count
      FROM favorites f
      INNER JOIN service_items si ON f.service_item_id = si.id
      WHERE f.user_id = ? 
        AND f.deleted_at IS NULL 
        AND si.deleted_at IS NULL 
        AND f.status = ?
    `;
    
    console.log('收藏总数查询SQL:', countQuery);
    console.log('查询参数:', [user_id, status]);
    
    const [countResult] = await promisePool.execute(countQuery, [user_id, status]);
    const totalCount = countResult[0].total_count;
    
    console.log('用户收藏总数:', totalCount);

    res.json({
      success: true,
      data: {
        userId: user_id,
        totalCount: totalCount,
        status: status
      },
      message: '获取用户收藏总数成功'
    });

  } catch (error) {
    console.error('获取用户收藏总数失败:', error);
    console.error('错误详情:', error.message);
    console.error('错误堆栈:', error.stack);
    
    // 检查是否是表不存在的错误
    if (error.message.includes("doesn't exist") || error.message.includes("Table") || error.code === 'ER_NO_SUCH_TABLE') {
      return res.status(500).json({
        success: false,
        message: '数据库表不存在，请联系管理员初始化数据库'
      });
    }
    
    res.status(500).json({
      success: false,
      message: '服务器内部错误',
      error: process.env.NODE_ENV === 'development' ? error.message : undefined
    });
  }
});


module.exports = router;
